package com.manaka.dao;

import com.manaka.bean.Message;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 和message表相关的数据库操作
 *
 * @author Manaka
 * @date 2018-09-05 00:42
 */
public class MessageDao {
    /**
     * 根据查询条件查询消息列表
     */
    public List<Message> queryMessageList(String command,String description){
        List<Message> messageList = new ArrayList<Message>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/micro_message","root","root");
            StringBuilder sql = new StringBuilder("select ID,COMMAND,DESCRIPTION,CONTENT from message where 1 ");
            List<String> paramList = new ArrayList<String>();
            if (command != null && !"".equals(command)){
                sql.append("and COMMAND = ?");
                paramList.add(command);
            }
            if (description != null && !"".equals(description.trim())){
                //%与?中间要有空格
                sql.append("and DESCRIPTION like '%' ? '%'");
                paramList.add(description);
            }
            PreparedStatement statement = conn.prepareStatement(sql.toString());
            for (int i = 0;i < paramList.size();i++){
                statement.setString(i+1,paramList.get(i));
            }
            ResultSet rs = statement.executeQuery();
            while (rs.next()){
                Message message = new Message();
                messageList.add(message);
                message.setId(rs.getString("ID"));
                message.setCommand(rs.getString("COMMAND"));
                message.setDescription(rs.getString("DESCRIPTION"));
                message.setContent(rs.getString("CONTENT"));
            }
//            req.setAttribute("messageList",messageList);
            conn.close();
            statement.close();
            rs.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return messageList;
    }
}
